package com.otc.common.dbhandler;
import java.sql.*;
import java.util.ArrayList;

import com.otc.common.model.*;
public class DBHandler {
	
	private Connection con;
	private Statement stmt;
	
	public void getConnection()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver");
			String url="jdbc:mysql://localhost:3306/test1";
			con = DriverManager.getConnection(url, "root", "");
			stmt = con.createStatement();
		}
		catch(ClassNotFoundException e)
		{
			e.printStackTrace();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}		
	}
	
	public void closeConnection()
	{
		try {
			con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public int executeDML(String query)
	{
		int res=0;
		try {
			res = stmt.executeUpdate(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}
	
	public String executeSelect(String query)
	{
		ResultSet rs = null;
		String res=null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			while(rs.next())
			{
				res=rs.getString(1);

			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return res;
	}
	
	public ResultSet executeSelect1(String query) {
	
		ResultSet rs = null;
		try
		{
		rs = stmt.executeQuery(query);
		
		}
		catch(SQLException s)
		{
			s.printStackTrace();
		}
		return rs;
		
		
	}
	
	/* Method : doUpdate()
	 * Returns : int
	 * Arguments : query - the query to be executed.
	 * Responsibility : to execute the update statements.
	 * */
	public int doUpdate(String query) {
		int res=-1;
			try {
				res = stmt.executeUpdate(query);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return(res);
	}
	
	/* Method : doInsert()
	 * Returns : void
	 * Arguments : query - the query to be executed.
	 * Responsibility : to execute the insert statements.
	 * */
	public boolean doInsert(String query) {
		boolean res=false;
			try {
				stmt.execute(query);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return(res);
	}
	
	/* Method : doSelect()
	 * Returns : ResultSet
	 * Arguments : query - the query to be executed.
	 * Responsibility : to execute the select statements.
	 * */
	public ResultSet doSelect(String query) {
		ResultSet rs=null;
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return(rs);
	}
	
	
	/* Method : doDelete()
	 * Returns : boolean
	 * Arguments : query - the query to be executed.
	 * Responsibility : to execute the delete statements.
	 * */
	public boolean doDelete(String query) {
		boolean res=true;
			try {
				res = stmt.execute(query);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return(res);
	}
	
	public int executeDML_Con(String query) 
	{
		int result = 0;
		try
		{	
			result=stmt.executeUpdate(query);
		}
		catch(SQLException c)
		{
			c.printStackTrace();
		}
				return result;
		
	}
	@SuppressWarnings("finally")
	public int executeCheckUser(String query1) 
	{
		int res=0;
		try
		{
			res=stmt.executeUpdate(query1);
		}
		catch(SQLException e)
		{
			e.printStackTrace();
		}	
		finally
		{
			return res;
		}
	}
	public String executeDispConId(String query)
	{
		ResultSet rs=null;
			String s2=null;
		try{
			rs=stmt.executeQuery(query);
			while(rs.next())
			{
				String s=rs.getString(1);
				s2=s;
			}
		}
		catch(SQLException e)
		{
			
		}
		return s2;
			
	}
	
	public int executeDMLDelete(String query)
	{
		int result = 0;
		try
		{
		 result = stmt.executeUpdate(query);

		}
		catch(SQLException c)
		{
			c.printStackTrace();
		}
		return result;
		
	}
	

	

	@SuppressWarnings("finally")
	public int executeDMLUpdate(String query) {
		int result=0;
		try 
		{
			result = stmt.executeUpdate(query);
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			return result;
		}
	}

	public int getNextValue()
	{
		String query="select comm_sequence.nextval from dual";
		ResultSet rs;
		int sequenceValue=0;
		try
		{
			rs = stmt.executeQuery(query);
			
			while(rs.next())
			{
				sequenceValue=rs.getInt(1);
			}
		}
		catch(Exception e)
		{
			e.printStackTrace();
		}
		return sequenceValue;
	}


	public ArrayList<Movies> retrieveMovieDetails(String query)
	{
		ResultSet rs = null;
		ArrayList<Movies> al = new ArrayList<Movies>();
		try {
			rs = stmt.executeQuery(query);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			while(rs.next())
			{
				String movieId=rs.getString("movie_id");
				String movieName=rs.getString("movie_name");
				String movieImage=rs.getString("movie_image");
				String movieUserId=rs.getString("user_id");
				String movieDesc=rs.getString("movie_description");
				String movieLikes=rs.getString("movie_like");
				String movieDislikes=rs.getString("movie_dislike");
				String movieRating=rs.getString("movie_rating");
				String movieTime=rs.getString("movie_time");
				//String movieCategoryId=rs.getString("movie_category_id");
				//System.out.println(movieId+movieName+movieImage+movieUserId+movieDesc+movieLikes+movieDislikes+movieRating+movieTime+"\n");
				Movies e = new Movies(
						movieId,
						movieName,
						movieImage,
						movieUserId,
						movieDesc,
						movieLikes,
						movieDislikes,
						movieRating,
						movieTime);
				al.add(e);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return al;
	}

}
